﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;

namespace PersonalAccounting.DataAccess
{
    public class DAOCuenta : IDAO<CuentaVO>
    {

        #region IDAO<CuentaVO> Members

        public bool Select(CuentaVO obj)
        {
            SQLiteCommand cmd = new SQLiteCommand();
            SQLiteParameter codigo;
            SQLiteDataReader reader;

            cmd.Connection = SQLiteConn.GetInstance().Connection;
            cmd.CommandText = QuerySelect();

            codigo = new SQLiteParameter("@codigo", obj.Codigo);

            cmd.Parameters.Add(codigo);

            reader = cmd.ExecuteReader();

            if (reader.Read())
            {
                Load(obj, reader);
                return true;
            }
            else
                return false;
        }

        public void Load(CuentaVO obj, SQLiteDataReader reader)
        {
            obj.Codigo = Convert.ToInt32(reader["CtaCodigo"]);
            obj.Nombre = Convert.ToString(reader["CtaNombre"]);

            IDAO<MonedaVO> dao = new DAOMoneda();
            dao.Load(obj.Moneda, reader);
        }


        public bool Insert(CuentaVO obj)
        {
            SQLiteCommand cmd = new SQLiteCommand();
            SQLiteParameter codigo, moneda, nombre;
            int records;

            cmd.Connection = SQLiteConn.GetInstance().Connection;
            cmd.CommandText = QueryInsert();

            codigo = new SQLiteParameter("@codigo", obj.Codigo);
            moneda = new SQLiteParameter("@moneda", obj.Moneda.Codigo);
            nombre = new SQLiteParameter("@nombre", obj.Nombre);

            cmd.Parameters.Add(codigo);
            cmd.Parameters.Add(moneda);
            cmd.Parameters.Add(nombre);

            records = cmd.ExecuteNonQuery();

            return (records > 0);
        }

        public bool Update(CuentaVO obj)
        {
            SQLiteCommand cmd = new SQLiteCommand();
            SQLiteParameter codigo, moneda, nombre;
            int records;

            cmd.Connection = SQLiteConn.GetInstance().Connection;
            cmd.CommandText = QueryUpdate();

            moneda = new SQLiteParameter("@moneda", obj.Moneda.Codigo);
            nombre = new SQLiteParameter("@nombre", obj.Nombre);
            codigo = new SQLiteParameter("@codigo", obj.Codigo);

            cmd.Parameters.Add(moneda);
            cmd.Parameters.Add(nombre);
            cmd.Parameters.Add(codigo);

            records = cmd.ExecuteNonQuery();

            return (records > 0);
        }

        public bool Delete(CuentaVO obj)
        {
            SQLiteCommand cmd = new SQLiteCommand();
            SQLiteParameter codigo;
            int records;

            cmd.Connection = SQLiteConn.GetInstance().Connection;
            cmd.CommandText = QueryDelete();

            codigo = new SQLiteParameter("@codigo", obj.Codigo);

            cmd.Parameters.Add(codigo);

            records = cmd.ExecuteNonQuery();

            return (records > 0);
        }


        public List<CuentaVO> GetAll()
        {
            SQLiteCommand cmd = new SQLiteCommand();
            SQLiteDataReader reader = null;
            List<CuentaVO> lista = new List<CuentaVO>();
            CuentaVO cuenta = null;

            cmd.Connection = SQLiteConn.GetInstance().Connection;
            cmd.CommandText = QueryGetAll();

            reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                cuenta = new CuentaVO();
                Load(cuenta, reader);
                lista.Add(cuenta);
            }

            return lista;
        }

        #endregion

        #region Public Methods

        public int GetNextCode()
        {
            SQLiteCommand cmd = new SQLiteCommand();
            SQLiteDataReader reader = null;
            int nextCode = 1;

            cmd.Connection = SQLiteConn.GetInstance().Connection;
            cmd.CommandText = QueryNextCode();

            reader = cmd.ExecuteReader();

            if (reader.Read())
                nextCode = Convert.ToInt32(reader["CtaCodigo"]);

            return nextCode;
        }

        #endregion


        #region SQL

        private string QuerySelect()
        {
            return "SELECT " + Campos() +
                   "FROM Ctas " +
                   "JOIN Monedas ON (Ctas.IdMoneda = Monedas.IdMoneda) " +
                   "WHERE IdCta = @codigo";
        }

        private string QueryGetAll()
        {
            return "SELECT " + Campos() +
                   "FROM Ctas " +
                   "JOIN Monedas ON (Ctas.IdMoneda = Monedas.IdMoneda) " +
                   "ORDER BY IdCta";
        }

        private string QueryNextCode()
        {
            return "SELECT Max(IdCta) + 1 AS CtaCodigo " +
                   "FROM Ctas";
        }

        public string Campos()
        {
            DAOMoneda dao = new DAOMoneda();
            return " Ctas.IdCta AS CtaCodigo," +
                   " Ctas.Nombre AS CtaNombre," +
                   dao.Campos();
        }



        private string QueryInsert()
        {
            return "INSERT INTO Ctas " +
                        "(IdCta, IdMoneda, Nombre) " +
                   "VALUES " +
                        "(@codigo, @moneda, @nombre)";
        }

        private string QueryUpdate()
        {
            return "UPDATE Ctas SET " +
                        "IdMoneda = @moneda, " +
                        "Nombre = @nombre " +
                   "WHERE IdCta = @codigo";
        }

        private string QueryDelete()
        {
            return "DELETE FROM Ctas WHERE IdCta = @codigo";
        }

        #endregion

    }
}